require 'rubygems'
require 'sqlite3'

class DatabaseClass
	def initialize
		if(FileTest.exists?('log.db')) then
			@logdb = SQLite3::Database.new("log.db")
		else
			@logdb = SQLite3::Database.new("log.db")
			sql ='create table recvmessages('
			sql+='id integer primary key'
			sql+=',type text not null' #TW or DM
			sql+=',message_id integer not null'
			sql+=',time text not null'
			sql+=',from_name text  not null'
			sql+=',message text  not null'
			sql+=');'
			@logdb.execute(sql)
			
			sql ='create table sendmessages('
			sql+='id integer primary key'
			sql+=',type text not null' #TW or DM
			sql+=',time text not null'
			sql+=',to_name text  not null'
			sql+=',message text  not null'
			sql+=');'
			@logdb.execute(sql)

			sql ='create table dataset('
			sql+='key text not null primary key' #repry or DM or tweet
			sql+=',value text not null'
			sql+=');'
			@logdb.execute(sql)
			sql ='insert into dataset values (:key,:value);'
			@logdb.execute(sql,:key=>'status',:value=>"normal")
			@logdb.execute(sql,:key=>'error_start',:value=>"")
			@logdb.execute(sql,:key=>'post_error',:value=>"0")
			@logdb.execute(sql,:key=>'latestTW',:value=>"0")
			@logdb.execute(sql,:key=>'latestDM',:value=>"0")
		end
	end

	def latest_DM(message_id)
		return update_dataset("latestDM",message_id)
	end

	def latest_TW(message_id)
		return update_dataset("latestTW",message_id)
	end

	def append_dataset(key,value)
		sql ='insert into dataset values (:key,:value);'
		begin
			return @logdb.execute(sql,:key=>key,:value=>value)
		rescue SQLite3::SQLException
			time = Time.now.strftime("%Y/%m/%d %H:%M:%S")
			puts "#{time}\t[ERROR:append_dataset] database access error"
		end
		return nil
	end

	def update_dataset(key,value)
		begin
			return @logdb.execute("update dataset set value='#{value}' where key='#{key}';")
		rescue SQLite3::SQLException
			time = Time.now.strftime("%Y/%m/%d %H:%M:%S")
			puts "#{time}\t[ERROR:update_dataset] database access error"
		end
		return nil
	end

	def get_dataset
		rows = @logdb.execute("select * from dataset;")
		return Hash[*rows.flatten]
	end

	def recvTW(message_id,time,from_name,message)
		append_recvmessage('TW',message_id,time,from_name,message)
	end
	def recvDM(message_id,time,from_name,message)
		append_recvmessage('DM',message_id,time,from_name,message)
	end
	def append_recvmessage(type,message_id,time,from_name,message)
		new_item={:type=>type,:message_id=>message_id,:time=>time,:from_name=>from_name,:message=>message}
		sql = "insert into recvmessages values (:id,:type,:message_id,:time,:from_name,:message);"
		begin
			@logdb.execute(sql,new_item)
		rescue SQLite3::SQLException
			time = Time.now.strftime("%Y/%m/%d %H:%M:%S")
			puts "#{time}\t[ERROR:append_recvmessages] database access error"
		end
	end
	
	def sendTW(to_name,message)
		time = Time.now.strftime("%Y/%m/%d %H:%M:%S")
		append_sendmessage('TW',time,to_name,message)
	end
	def rsendDM(to_name,message)
		time = Time.now.strftime("%Y/%m/%d %H:%M:%S")
		append_sendmessage('DM',time,to_name,message)
	end
	def append_sendmessage(type,time,from_name,message)
		new_item={:type=>type,:time=>time,:to_name=>from_name,:message=>message}
		sql = "insert into sendmessages values (:id,:type,:time,:to_name,:message);"
		begin
			@logdb.execute(sql,new_item)
		rescue SQLite3::SQLException
			time = Time.now.strftime("%Y/%m/%d %H:%M:%S")
			puts "#{time}\t[ERROR:append_sendmessages] database access error"
		end
	end

	def getmessage(sql_option='')
		sql = "select * from message #{sql_option};"
		return @logdb.execute(sql)
	end

	def close
		@logdb.close
	end

	
end
